A functional dependency (FD) is a constraint between two sets of attributes in a relation from a database.
Given a relation R, a set of attributes X in R is said to functionally determine another attribute Y, also in R, (written X → Y) if, and only if, each X value is associated with precisely one Y value. Customarily we call X the determinant set and Y the dependent attribute. Thus, given a tuple and the values of the attributes in X, one can determine the corresponding value of the Y attribute. In simple words, if X value is known, Y value is certainly known. For the purposes of simplicity, given that X and Y are sets of attributes in R, X → Y denotes that X functionally determines each of the members of Y—in this case Y is known as the dependent set. Thus, a candidate key is a minimal set of attributes that functionally determine all of the attributes in a relation.
A functional dependency FD: X → Y is called trivial if Y is a subset of X.
The determination of functional dependencies is an important part of designing databases in the relational model, and in database normalization and denormalization. The functional dependencies, along with the attribute domains, are selected so as to generate constraints that would exclude as much data inappropriate to the user domain from the system as possible.
For example, suppose one is designing a system to track vehicles and the capacity of their engines. Each vehicle has a unique vehicle identification number (VIN). One would write VIN → EngineCapacity because it would be inappropriate for a vehicle's engine to have more than one capacity. (Assuming, in this case, that vehicles only have one engine.) However, EngineCapacity → VIN, is incorrect because there could be many vehicles with the same engine capacity.
This functional dependency may suggest that the attribute EngineCapacity be placed in a relation with candidate key VIN. However, that may not always be appropriate. For example, if that functional dependency occurs as a result of the transitive functional dependencies VIN → VehicleModel and VehicleModel → EngineCapacity then that would not result in a normalized relation.
Contents |
A functional depending set S is irreducible if the set has following three properties:
Sets of Functional Dependencies(FD) with these properties are also called canonical or minimal.
Given that X, Y, and Z are sets of attributes in a relation R, one can derive several properties of functional dependencies. Among the most important are Armstrong's axioms, which are used in database normalization:
From these rules, we can derive these secondary rules:
Equivalent sets of functional dependencies are called covers of each other. Every set of functional dependencies has a canonical cover.
This example illustrates the concept of functional dependency. The situation modeled is that of college students visiting one or more lectures in each of which they are assigned a teaching assistant (TA). Let's further assume that every student is in some semester and is identified by a unique integer ID.
| StudentID | Semester | Lecture | TA |
|---|---|---|---|
| 1234 | 6 | Numerical Methods | John |
| 2380 | 4 | Numerical Methods | Peter |
| 1234 | 6 | Visual Computing | Amina |
| 1201 | 4 | Numerical Methods | Peter |
| 1201 | 4 | Physics II | Simone |
We notice that whenever two rows in this table feature the same StudentID, they also necessarily have the same Semester values. This basic fact can be expressed by a functional dependency:
Other nontrivial functional dependencies can be identified, for example:
The latter expresses the fact that the set {StudentID, Lecture} is a superkey of the relation.